package com.wzcl.app.dao.repository;

import com.wzcl.app.dao.model.Course;
import com.wzcl.app.dao.model.UserCourse;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

public interface UserCourseRepository extends JpaRepository<UserCourse,Integer>, JpaSpecificationExecutor<UserCourse> {

    @Query("SELECT m FROM UserCourse m WHERE m.userPhone = ?1 AND m.course.id = ?2 AND m.spec IS NULL")
    UserCourse findByPhoneAndCourseIdAndSpecNull(String userPhone, Integer courseId);

    @Query("SELECT m FROM UserCourse m WHERE m.userPhone = ?1 AND m.course.id = ?2 AND m.spec = ?3")
    UserCourse findByPhoneAndCourseIdAndSpec(String userPhone, Integer courseId, String spec);

    @Query("SELECT m FROM UserCourse m WHERE m.userPhone = ?1 AND m.course.id = ?2")
    List<UserCourse> findByPhoneAndCourseId(String userPhone, Integer courseId);

    @Query(value = "insert into user_course(user_id, phone, course_id) values(?1, ?2 ,?3)",nativeQuery = true)
    UserCourse insertUserCourse(Integer userId, String phone, Integer courseId);

    @Query(value = "select u from UserCourse u where u.userPhone = ?1 order by u.createTime desc")
    List<UserCourse> findAllByUserPhone(String userPhone);

    //根据课程id删除用户课程
    @Query(value = "delete from UserCourse u where u.course.id = ?1 and u.userPhone = ?2")
    @Modifying
    @Transactional
    void deleteByCourseAndPhone(Integer id, String userPhone);

    //根据课程id查询课程
    @Query(value = "select u from UserCourse u where u.course.id = ?1 and u.userPhone = ?2")
    UserCourse findAllByCourseIdAndUserPhone(Integer id, String userPhone);

    @Query(value = "delete from UserCourse uc where uc.course.id = ?1")
    @Modifying
    @Transactional
    void deleteByCourseId(Integer courseId);

    @Query(value = "select uc.course from UserCourse uc where uc.userId = ?1")
    List<Course> findUserCourseByUserId(Integer userId);

    //查询用户已经购买的某个课程类型的所有课程
    @Query(value = "select uc from UserCourse uc where uc.userPhone = ?1 and uc.course.courseType = ?2")
    List<UserCourse> findAllByCourseType(String userPhone, Integer courseType);
}
